﻿ALTER PROCEDURE dbo.GroupKonsMake
	(
		@K_GROUP int,
		@K_KLIENT int,
		@Date char(20),
		@TimeObrsach char(20),
		@TimeKons char(20)
	)
-- Создание КПО на основании КПО и данных групповой консультации
AS

    declare @K_RAB  int 
    declare @K_SPEC int
    declare	@K_ZAN_GROUP int
    declare @N_PROBLEM nvarchar(1024)
    declare @N_MERI nvarchar(1024)
       
    -- Определение параметров консультации
    select top 1 @K_RAB = K_RAB, @K_SPEC = K_KONSULTANT, @K_ZAN_GROUP = K_ZAN, @N_PROBLEM = N_PROBLEM, @N_MERI = N_MERI
    from b_group where K_GROUP = @K_GROUP

	declare @K_PRIZN	int	-- Признак консульатации на жернале обращения
    declare @K_POM		int -- Код типа помощи на консультации УКС
    declare @K_KONS     int -- Код проведенной консультации УКС
    declare @K_ZAN      int -- Код занятия на проведенной консультации УКС

    if @K_ZAN_GROUP = 1			-- Групповая работы
    begin
        set @K_POM = 5
        set @K_PRIZN = 4
        set @K_ZAN = 1
        set @K_KONS = 5
    end
    else                    -- Кулбная работа
    begin
        set @K_POM = 10
        set @K_PRIZN = 5
        set @K_ZAN = 4
        set @K_KONS = 10
    end
    
	declare @lastK_KPO int
	-- получаем номер последней предыдущей КПО и пользуя фамилии
	set @lastK_KPO = (SELECT     max(b_kpo.K_KPO)
	FROM         b_group_klient INNER JOIN
	                      b_klient ON b_group_klient.K_KLIENT = b_klient.K_KLIENT INNER JOIN
	                      b_uks ON b_klient.K_UKS = b_uks.K_UKS INNER JOIN
	                      b_kpo ON b_uks.K_UKS = b_kpo.K_UKS AND ISNULL(b_klient.N_IM, '') = ISNULL(b_kpo.N_IM, '') AND ISNULL(b_klient.N_OT, '') = ISNULL(b_kpo.N_OT, 
	                      '') AND ISNULL(b_klient.N_FA, '') = ISNULL(b_kpo.N_FA, '')
	WHERE     b_group_klient.K_KLIENT = @K_KLIENT)

	-- Создание новой КПО на основании данных клиента и УКС
	INSERT INTO b_kpo
						  (K_POL, GOD_R, K_OBR, K_SOCSTAT, K_RAB, K_ADR, K_INF_CENTR, K_SP, K_PRICH, K_UKS, K_SPEC, D_OBRASCH, V_OBRASCH, D_KONS, 
						  V_KONS, K_OBRASCH, N_FA, N_IM, N_OT)
	SELECT     b_klient.K_POL, b_klient.GOD_R, b_klient.K_OBR, b_klient.K_SOCSTAT, @K_RAB AS K_RAB, b_uks.K_ADR, b_uks.K_INF_CENTR, b_uks.K_SP, 
						  b_uks.K_PRICH, b_uks.K_UKS, @K_SPEC AS K_SPEC, @Date AS DateOblash, @TimeObrsach AS TimeObrsach, @Date AS DateKons, 
						  @TimeKons AS TimeKons, 1 AS K_OBRASCH, N_FA, N_IM, N_OT
	FROM         b_uks INNER JOIN
						  b_klient ON b_uks.K_UKS = b_klient.K_UKS INNER JOIN
						  b_group INNER JOIN
						  b_group_klient ON b_group.K_GROUP = b_group_klient.K_GROUP ON b_klient.K_KLIENT = b_group_klient.K_KLIENT
	WHERE	b_group.K_GROUP = @K_GROUP and b_klient.K_KLIENT = @K_KLIENT
		
	declare @newK_KPO int
	
	set @newK_KPO  = (SELECT SCOPE_IDENTITY())
	 	
	-- запивываем инфу в sv_kpo_pom
	INSERT INTO sv_kpo_pom (K_KPO, K_POM) VALUES (@newK_KPO, @K_POM)
	
	-- получаем первую причину из последнего КПО 
	declare @lastK_PRICH int
	set @lastK_PRICH = (Select top 1 K_PRICH from sv_kpo_prich where K_KPO = @lastK_KPO)

	-- Добавляем причину и последней КПО
	INSERT INTO sv_kpo_prich (K_KPO, K_PRICH) VALUES (@newK_KPO ,@lastK_PRICH)
	                   
	-- Получаем код поля информации о семье из последней КПО
	declare @lastK_SOSTAV_SEMYI int
	set @lastK_SOSTAV_SEMYI = (Select top 1 K_SOSTAV_SEMYI from b_sostav_semyi_kpo where K_KPO = @lastK_KPO)
           
	-- Копируем информацию о семье  в b_sostav_semyi_uks
	INSERT INTO b_sostav_semyi_kpo (K_KPO, N_FA, N_IM, N_OT, GOD_R, C_LET, K_POL, K_RODSTV_OTN, N_PRIM )
	SELECT     @newK_KPO AS newK_KPO, N_FA, N_IM, N_OT, GOD_R, C_LET, K_POL, K_RODSTV_OTN, N_PRIM
	FROM         b_sostav_semyi_kpo
	WHERE     (K_KPO = @lastK_KPO)
	
	-- Обновляем запись КПО данными из предыдущей КПО
	UPDATE    b_kpo
	SET              K_UKS = K_UKS, K_POL = K_POL, GOD_R = GOD_R, K_SP = K_SP, F_DETI = F_DETI, K_UP = K_UP, K_OBR = K_OBR, K_SOCSTAT = K_SOCSTAT, 
	                      K_INF_CENTR = K_INF_CENTR, N_PRIM = N_PRIM, F_KLIENT = F_KLIENT, N_FA = N_FA, N_IM = N_IM, N_OT = N_OT
	WHERE     (K_KPO = @newK_KPO) AND (K_KPO = @lastK_KPO)

	--  Добавление инфы в b_uks_konst
	INSERT INTO b_uks_kons (K_UKS, D_KONS, K_KPO, K_KLIENT, K_RAB , N_PROB, N_MERY, K_KONS, K_ZAN ) 
	SELECT     b_kpo.K_UKS, b_kpo.D_KONS, b_kpo.K_KPO, b_klient.K_KLIENT, @K_SPEC AS K_RAB, @N_PROBLEM AS N_PROB, @N_MERI AS N_MERY, 
	                      @K_KONS AS K_KONS, @K_ZAN AS K_ZAN
	FROM         b_klient INNER JOIN
	                      b_kpo ON b_klient.K_UKS = b_kpo.K_UKS
	WHERE     (b_kpo.K_KPO = @newK_KPO) AND (b_klient.K_KLIENT = @K_KLIENT)
	
	-- Добавление групповых консультаций в журнал обращений
	INSERT INTO b_zhurnal_obrasch (N_FA, N_IM, N_OT, K_RAB, D_OBRASCH, V_OBRASCH, F_PROVEDENA, K_OBRASCH, D_KONS, V_KONS, K_KPO, K_PRIZN, K_RAB_ZAPOL)
	SELECT     b_klient.N_FA, b_klient.N_IM, b_klient.N_OT, b_group.K_KONSULTANT, @Date AS D_OBRASCH, @TimeObrsach AS V_OBRASCH, 1 AS F_PROVEDENA, 
	                      1 AS K_OBRASCH, @Date AS D_KONS, @TimeKons AS V_KONS, @newK_KPO AS K_KPO, @K_PRIZN AS K_PRIZN, @K_RAB AS K_RAB_ZAPOL
	FROM         b_group INNER JOIN
	                      b_group_klient ON b_group.K_GROUP = b_group_klient.K_GROUP INNER JOIN
	                      b_klient ON b_group_klient.K_KLIENT = b_klient.K_KLIENT
	WHERE     (b_group_klient.F_NE_PRISHOL = 0) AND (b_group.K_GROUP = @K_GROUP) AND (b_klient.K_KLIENT = @K_KLIENT);

	
	RETURN